package com.example.sports_meet;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import org.jetbrains.annotations.Nullable;

public class MyDbHelper extends SQLiteOpenHelper {
    public MyDbHelper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }


    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE athlete (ano CHAR(9) PRIMARY KEY, aname CHAR(20), asex CHAR(2) DEFAULT '男', aage SMALLINT, assort CHAR(20));");

        db.execSQL("CREATE TABLE eevent (eno CHAR(9) PRIMARY KEY, ename CHAR(20), etime DATETIME, fno CHAR(9), jno CHAR(9), sno CHAR(9), esex CHAR(2));");

        db.execSQL("CREATE TABLE form (fno CHAR(9) PRIMARY KEY, fname CHAR(20));");

        db.execSQL("CREATE TABLE judge (jno CHAR(9) PRIMARY KEY, jname CHAR(25), jsex CHAR(2) DEFAULT '男', jage CHAR(20));");

        db.execSQL("CREATE TABLE signup (ano CHAR(9), aname CHAR(20),eno CHAR(9),ename CHAR(20), sutime DATETIME, suwhether CHAR(10) DEFAULT '是', suvalid CHAR(2) DEFAULT '是', PRIMARY KEY (ano, eno));");

        db.execSQL("CREATE TABLE site (sno CHAR(9) PRIMARY KEY, sname CHAR(20), ssize CHAR(10), scapacity CHAR(10));");

        db.execSQL("CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, username NVARCHAR(50), password NVARCHAR(50), type NVARCHAR(50), email NVARCHAR(50), image BLOB);");

        db.execSQL("CREATE TABLE vapply (vno CHAR(9),vname CHAR(20),eno CHAR(9),ename CHAR(20), vatime DATETIME, whetherentry CHAR(10) DEFAULT '是', PRIMARY KEY (eno, vno));");

        db.execSQL("CREATE TABLE volunteer (vno CHAR(9) PRIMARY KEY, vname CHAR(20), vsex CHAR(2) DEFAULT '男', vage CHAR(20),vemail CHAR(30),ename CHAR(30));");

        db.execSQL("CREATE TABLE worker (wno CHAR(9) PRIMARY KEY, wname CHAR(20), wsex CHAR(2) DEFAULT '男', wage CHAR(20), sno CHAR(9));");

        // 在athlete表中插入数据
        insertAthletesData(db);

        // 在 eevent 表中插入数据
        insertEeventData(db);

        // 在 form 表中插入数据
        insertFormData(db);

        // 在 judge 表中插入数据
        insertJudgeData(db);

        // 在 signup 表中插入数据
        insertSignupData(db);

        // 在 site 表中插入数据
        insertSiteData(db);

        // 在 vapply 表中插入数据
        insertVapplyData(db);

        // 在 volunteer 表中插入数据
        insertVolunteerData(db);

        //在worker表中插入数据
        insertWorkerData(db);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {// 检查旧版本和新版本，根据需要进行数据库升级
        if (oldVersion == 1 && newVersion == 2) {
            String addAnameColumn = "ALTER TABLE signup ADD COLUMN aname TEXT";
            String addEnameColumn = "ALTER TABLE signup ADD COLUMN ename TEXT";
            db.execSQL(addAnameColumn);
            db.execSQL(addEnameColumn);
            String addvnameColumn = "ALTER TABLE vapply ADD COLUMN vname TEXT";
            db.execSQL(addvnameColumn);
        }

    }

    public void insertAthletesData(SQLiteDatabase db) {
        String sql = "INSERT INTO athlete (ano, aname, asex, aage, assort) VALUES (?, ?, ?, ?, ?)";

        Object[][] athletesDataArray = new Object[][]{
                {"202201002", "王欣欣", "女", 19, "学生"},
                {"202201010", "孙子威", "男", 21, "学生"},
                {"202201003", "赵亚杰", "女", 20, "学生"},
                {"202201004", "王玲玲", "女", 19, "学生"},
                {"202201005", "刘雅洁", "女", 21, "学生"}
        };

        for (Object[] athleteData : athletesDataArray) {
            db.execSQL(sql, athleteData);
        }
    }

    public void insertEeventData(SQLiteDatabase db) {
        String sql = "INSERT INTO eevent (eno, ename, etime, fno, jno, sno, esex) VALUES (?, ?, ?, ?, ?, ?, ?)";

        Object[][] eeventDataArray = new Object[][]{
                {"202204001", "男子100米自由泳", "2022-06-13 08:00:00", "202207001", "202203001", "202205001", "男"},
                {"202204002", "女子100米自由泳", "2022-06-13 08:30:00", "202207001", "202203001", "202205001", "女"},
                {"202204003", "女子水上芭蕾", "2022-06-13 10:00:00", "202207001", "202203002", "202205001", "女"},
                {"202204004", "男子水上芭蕾", "2022-06-13 10:30:00", "202207001", "202203002", "202205001", "男"},
                {"202204005", "男子100米仰泳", "2022-06-13 11:30:00", "202207001", "202203002", "202205001", "男"},
                {"202204006", "女子100米仰泳", "2022-06-13 12:00:00", "202207001", "202203002", "202205001", "女"},
                {"202204007", "男子10米气手枪", "2022-06-13 08:00:00", "202207002", "202203001", "202205002", "男"},
                {"202204008", "女子10米气手枪", "2022-06-13 08:30:00", "202207002", "202203001", "202205002", "女"},
                {"202204009", "女子飞碟多向", "2022-06-13 10:00:00", "202207002", "202203002", "202205002", "女"},
                {"202204010", "男子飞碟多向", "2022-06-13 10:30:00", "202207002", "202203002", "202205002", "男"},
                {"202204011", "男子25米手枪速射", "2022-06-13 11:30:00", "202207002", "202203002", "202205002", "男"},
                {"202204012", "女子25米手枪速射", "2022-06-13 12:00:00", "202207002", "202203002", "202205002", "女"}
        };

        for (Object[] eeventData : eeventDataArray) {
            db.execSQL(sql, eeventData);
        }
    }

    public void insertFormData(SQLiteDatabase db) {
        String sql = "INSERT INTO form (fno, fname) VALUES (?, ?)";

        Object[][] formDataArray = new Object[][]{
                {"202207001", "水上项目"},
                {"202207002", "射击类"},
                {"202207003", "田径"},
                {"202207004", "冰上运动"}
        };

        for (Object[] formData : formDataArray) {
            db.execSQL(sql, formData);
        }
    }

    public void insertJudgeData(SQLiteDatabase db) {
        String sql = "INSERT INTO judge (jno, jname, jsex, jage) VALUES (?, ?, ?, ?)";

        Object[][] judgeDataArray = new Object[][]{
                {"202203004", "李宇航", "男", 40},
                {"202203001", "安袁", "女", 30},
                {"202203002", "宇文豪", "男", 37},
                {"202203003", "吴步军", "男", 35},
                {"202203005", "杨凯歌", "男", 32}
        };

        for (Object[] judgeData : judgeDataArray) {
            db.execSQL(sql, judgeData);
        }
    }

    public void insertSignupData(SQLiteDatabase db) {
        String sql = "INSERT INTO signup (ano,aname, eno,ename, sutime, suwhether, suvalid) VALUES (?,?,?, ?, ?, ?, ?)";

        Object[][] signupDataArray = new Object[][]{
                {"202201002", "王欣欣", "202204011", "男子25米手枪速射", "2022-06-10 14:30:00", "是", "是"},
                {"202201003", "孙子威", "202204003", "女子水上芭蕾", "2022-06-11 13:00:00", "是", "是"},
                {"202201003", "孙子威", "202204009", "女子飞碟多向", "2022-06-12 18:00:00", "是", "是"}};

        for (Object[] signupData : signupDataArray) {
            db.execSQL(sql, signupData);
        }
    }

    public void insertSiteData(SQLiteDatabase db) {
        String sql = "INSERT INTO site (sno, sname, ssize, scapacity) VALUES (?, ?, ?, ?)";

        Object[][] siteDataArray = new Object[][]{
                {"202205001", "游泳馆", 70000, 15000},
                {"202205002", "射击场", 204000, 91000},
                {"202205003", "田径场", 66124, 7508},
                {"202205004", "室内冰场", 220000, 5000}
        };

        for (Object[] siteData : siteDataArray) {
            db.execSQL(sql, siteData);
        }
    }

    public void insertVapplyData(SQLiteDatabase db) {
        String sql = "INSERT INTO vapply (vno,vname,eno,ename,vatime, whetherentry) VALUES (?,?, ?, ?, ?, ?)";
        Object[][] vapplyDataArray = new Object[][]{
                {"202201002", "王欣欣", "202204011", "男子25米手枪速射", "2022-06-10 14:30:00", "是"},
                {"202201003", "孙子威", "202204003", "女子水上芭蕾", "2022-06-11 13:00:00", "是",},
                {"202201003", "孙子威", "202204009", "女子飞碟多向", "2022-06-12 18:00:00", "是",}
        };


        for (Object[] vapplyData : vapplyDataArray) {
            db.execSQL(sql, vapplyData);
        }
    }

    public void insertVolunteerData(SQLiteDatabase db) {
        String sql = "INSERT INTO volunteer (vno, vname, vsex, vage) VALUES (?, ?, ?, ?)";

        Object[][] volunteerDataArray = new Object[][]{
                {"202202001", "陈帅昂", "男", 20},
                {"202202002", "董梦香", "女", 23},
                {"202202003", "郭肖迪", "女", 22},
                {"202202004", "黄晨豪", "男", 22}
        };

        for (Object[] volunteerData : volunteerDataArray) {
            db.execSQL(sql, volunteerData);
        }
    }

    public void insertWorkerData(SQLiteDatabase db) {
        String sql = "INSERT INTO worker (wno, wname, wsex, wage, sno) VALUES (?, ?, ?, ?, ?)";

        Object[][] workerDataArray = new Object[][]{
                {"202206001", "张锦淼", "男", 30, "202205001"},
                {"202206002", "蔡梦妍", "女", 35, "202205004"},
                {"202206003", "郭广奥", "男", 36, "202205002"},
        };

        for (Object[] workerData : workerDataArray) {
            db.execSQL(sql, workerData);
        }
    }
}
